"""
Case Type   : 函数支持指定并行相关参数
Case Name   : 直接在查询中使用嵌套游标--单层嵌套
Create At   : 2024/10/12
Owner       : @kyleze
Description :
    1.创建测试表
    2.插入数据
    3.在事务中定义和访问单层嵌套游标
    4.清理环境
Expect      :
    1.成功
    2.成功
    3.只展示游标名，无数据
    4.成功
History     :
"""
import os
import unittest

from testcase.utils.CommonSH import CommonSH
from testcase.utils.Logger import Logger
from testcase.utils.Constant import Constant


class ParallelEnable(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        self.log.info(f'----{os.path.basename(__file__)} start----')
        self.primary_sh = CommonSH('PrimaryDbUser')
        self.constant = Constant()
        self.table_name = 't_cursor_0005'

    def test_paralle(self):
        step = '----step1:创建测试表 expect:成功----'
        self.log.info(step)
        create_sql = f"drop table if exists {self.table_name};" \
                     f"create table {self.table_name}(employees_id int, " \
                     f"name varchar(50), department_id int, salary int, " \
                     f"birth_date date);"
        create_res = self.primary_sh.execut_db_sql(create_sql)
        self.log.info(create_res)
        self.assertIn(
            self.constant.CREATE_TABLE_SUCCESS, create_res, '执行失败' + step)

        step = '----step2:插入数据 expect:成功----'
        self.log.info(step)
        ins_sql = f"insert into {self.table_name} values (1, 'zhangsan', 1, " \
                  f"1000, '2001-10-10'),(2, 'lisi', 1, 1000, '1998-10-10')," \
                  f"(3, 'wangwu', 2, 2000, '2002-10-10'),(4, 'heliu', 2, " \
                  f"2000, '2003-10-10');" \
                  f"insert into {self.table_name} values (5, 'zhangzhang', " \
                  f"2, 1500, '2001-11-10'),(6, 'mili', 1, 1800, " \
                  f"'1999-12-18'),(7, 'liuliu', 2, 2000, '2002-10-10'),(8, " \
                  f"'houge', 1, 2000, '2000-11-11');"
        ins_res = self.primary_sh.execut_db_sql(ins_sql)
        self.log.info(ins_res)
        self.assertEqual(2, ins_res.count(self.constant.INSERT_SUCCESS_MSG),
                         '执行失败' + step)

        step = '----step3:直接在查询中使用嵌套游标 expect:只展示游标名，无数据----'
        self.log.info(step)
        tra_sql = f"select t1.name, cursor(select t2.name from " \
                  f"{self.table_name} t2) ming from {self.table_name} t1;"
        transaction_res = self.primary_sh.execut_db_sql(tra_sql)
        self.log.info(transaction_res)
        self.assertIn('8 rows', transaction_res, '执行失败' + step)

    def tearDown(self):
        self.log.info('----清理环境----')
        table_res = \
            self.primary_sh.execut_db_sql(f'drop table {self.table_name};')
        self.log.info(table_res)
        self.assertIn(self.constant.DROP_TABLE_SUCCESS, table_res, '删表失败')
        self.log.info(f'----{os.path.basename(__file__)} end----')
